﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FortuneHeavenCommon.DataMap;
using System.Data.SqlClient;

namespace FortuneHeavan.Game.DAL
{
    public class cftt_zsjfDAL : UserDBBase
    {
        public List<cftt_zsjf> GetList(int page, int size, string fromuser,string touser)
        {
            string condition = "where 1=1  ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(fromuser) && fromuser.Trim() != "")
            {
                condition += " and fromuser =@fromuser";
                content.Add(new SqlParameter("@fromuser", fromuser));
            }
            if (!string.IsNullOrEmpty(touser) && touser.Trim() != "")
            {
                condition += " and touser =@touser";
                content.Add(new SqlParameter("@touser", touser));
            }
             content.Add(new SqlParameter("@p0", size * (page - 1) + 1));
            content.Add( new SqlParameter("@p1", size * page));
            string sql = @"SELECT * FROM (select  [id],[createdate],[fromuser],[touser],[jf] ,ROW_NUMBER() OVER(ORDER BY ID DESC) ROW   
from  dbo.cftt_zsjf {0}
 ) FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";

            List<cftt_zsjf> list = Query<cftt_zsjf>(string.Format(sql, condition), content.ToArray());
                return list;
        }
        public int GetTotalSize(string fromuser, string touser)
        {
            string condition = "where 1=1  ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(fromuser) && fromuser.Trim() != "")
            {
                condition += " and fromuser =@fromuser";
                content.Add(new SqlParameter("@fromuser", fromuser));
            }
            if (!string.IsNullOrEmpty(touser) && touser.Trim() != "")
            {
                condition += " and touser =@touser";
                content.Add(new SqlParameter("@touser", touser));
            }
            string sql = @"select count(id) from cftt_zsjf {0}";

            return RunSQL(string.Format(sql, condition), content.ToArray());
            
        }

        public List<cftt_zsjf> GetListAll(int page, int size, string fromuser, string touser)
        {
            string condition = "where 1!=1  ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(fromuser) && fromuser.Trim() != "")
            {
                condition += " or fromuser =@fromuser";
                content.Add(new SqlParameter("@fromuser", fromuser));
            }
            if (!string.IsNullOrEmpty(touser) && touser.Trim() != "")
            {
                condition += " or touser =@touser";
                content.Add(new SqlParameter("@touser", touser));
            }
            content.Add(new SqlParameter("@p0", size * (page - 1) + 1));
            content.Add(new SqlParameter("@p1", size * page));
            string sql = @"SELECT * FROM (select  [id],[createdate],[fromuser],[touser],[jf] ,ROW_NUMBER() OVER(ORDER BY ID DESC) ROW   
from  dbo.cftt_zsjf {0}
 ) FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";

            List<cftt_zsjf> list = Query<cftt_zsjf>(string.Format(sql, condition), content.ToArray());
            return list;
        }
        public int GetTotalSizeAll(string fromuser, string touser)
        {
            string condition = "where 1!=1  ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(fromuser) && fromuser.Trim() != "")
            {
                condition += " or fromuser =@fromuser";
                content.Add(new SqlParameter("@fromuser", fromuser));
            }
            if (!string.IsNullOrEmpty(touser) && touser.Trim() != "")
            {
                condition += " or touser =@touser";
                content.Add(new SqlParameter("@touser", touser));
            }
            string sql = @"select count(id) from cftt_zsjf {0}";

            return RunSQL(string.Format(sql, condition), content.ToArray());

        }


        public List<cftt_zsjf> GetList( string fromuser, string touser)
        {
            string condition = "where 1=1  ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(fromuser) && fromuser.Trim() != "")
            {
                condition += " and fromuser =@fromuser";
                content.Add(new SqlParameter("@fromuser", fromuser));
            }
            if (!string.IsNullOrEmpty(touser) && touser.Trim() != "")
            {
                condition += " and touser =@touser";
                content.Add(new SqlParameter("@touser", touser));
            }
            string sql = @"select  [id],[createdate],[fromuser],[touser],[jf] ,ROW_NUMBER() OVER(ORDER BY createdate ASC) ROW   
from  dbo.cftt_zsjf {0}";

            List<cftt_zsjf> list = Query<cftt_zsjf>(string.Format(sql, condition), content.ToArray());
            return list;
        }

        public int GetTotalScore(string fromuser, string touser)
        {
            string condition = "where 1=1  ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(fromuser) && fromuser.Trim() != "")
            {
                condition += " and fromuser =@fromuser";
                content.Add(new SqlParameter("@fromuser", fromuser));
            }
            if (!string.IsNullOrEmpty(touser) && touser.Trim() != "")
            {
                condition += " and touser =@touser";
                content.Add(new SqlParameter("@touser", touser));
            }
            string sql = @"select  sum([jf])   from  dbo.cftt_zsjf {0}";


            return RunSQL(string.Format(sql, condition), content.ToArray());

        }
    }
}
